oracle 日期函数

sysdate

常用：（sysdate,'YYYY-MM-DD HH24:MI:SS'）

注：sql中星期是1-7，是周日开始的，周日是1，周一是2

YYYY                表示年
YYY                 表示年后3位数
YY                  表示年后2位数
Y                   表示年最1位数
MONTH               表示月（全称）
MM                  表示当年第几月
DDD                 表示当年第几天
DD                  表示当月第几天
DAY                 表示星期几（全称）
D                   表示星期几
HH                  表示当前小时 （12小时制）
HH24                表示当前小时 （24小时制）
MI                  表示分钟
SS                  表示秒
Q                   表示季度（1-4）
WW                  表示年中的第几个星期 
W                   表示月中的第几个星期 

---
SQL> select sysdate from dual;
SYSDATE
-----------
2024/4/22 2

SQL> select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH2
------------------------------
2024-04-22 20:56:17


--把小时取整四舍五入
SQL> select round(sysdate) from dual;
ROUND(SYSDATE)
--------------
2024/4/23

--把小时取整
SQL> select trunc(sysdate) from dual;
TRUNC(SYSDATE)
--------------
2024/4/22

SQL> 

months_between 计算两个日期相差的月份返回一个数字，第一个日期>第二个日期得到正数，反之负数

语法：months_between(日期1，日期2)

SQL> SELECT MONTHS_BETWEEN (SYSDATE,HIREDATE ) FROM EMP;
MONTHS_BETWEEN(SYSDATE,HIREDAT
------------------------------
               520.18969422043
              518.092920026882
                           518
              516.673565188172
              510.834855510753
              515.705823252688
              514.447758736559
              444.125178091398
               509.18969422043
              511.480016801075
              442.996145833333
              508.641307123656
              508.641307123656
              506.996145833333
14 rows selected

SQL> 
SQL> 
SQL> SELECT TRUNC(MONTHS_BETWEEN (SYSDATE,HIREDATE )) FROM EMP;
TRUNC(MONTHS_BETWEEN(SYSDATE,H
------------------------------
                           520
                           518
                           518
                           516
                           510
                           515
                           514
                           444
                           509
                           511
                           442
                           508
                           508
                           506


--打印入职人员姓名，及入职的时长(月)，并按入职时长来排序。                           
SQL> select ename, round(months_between (sysdate,hiredate)) from emp order by months_between (sysdate,hiredate) desc;
ENAME      ROUND(MONTHS_BETWEEN(SYSDATE,H
---------- ------------------------------
SMITH                                 520
ALLEN                                 518
WARD                                  518
JONES                                 517
BLAKE                                 516
CLARK                                 514
TURNER                                511
MARTIN                                511
KING                                  509
JAMES                                 509
FORD                                  509
MILLER                                507
SCOTT                                 444
ADAMS                                 443
14 rows selected 






--打印源表，增加date2为离职时间                          
SQL> select * from emp2;
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO DATE2       DATE22
----- ---------- --------- ----- ----------- --------- --------- ------ ----------- -----------
 7369 SMITH      CLERK      7902 1980/12/17     800.00               20 1993/12/3   
 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30 1992/1/5    
 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30 1998/3/9    
 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20 1987/4/7    
 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.10   1400.00     30             
 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30             
 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10             
 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20             
 7839 KING       PRESIDENT       1981/11/17    5000.00               10             
 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30             
 7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20             
 7900 JAMES      CLERK      7698 1981/12/3      950.00               30             
 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20             
 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10             
14 rows selected


--date2为离职时间，hiredate为入职时间
--打印所有员工，按离职时间排序，在职的输入为working
--注：working为字符，所有nvl对比时也需要是字符，所以to_char将数字转为字符
SQL> select ename, nvl(to_char(round(months_between(date2,hiredate))),'working') from emp2 order by months_between(date2,hiredate) ;
ENAME      NVL(TO_CHAR(ROUND(MONTHS_BETWE
---------- ----------------------------------------
JONES      72
ALLEN      131
SMITH      156
WARD       205
KING       working
TURNER     working
ADAMS      working
JAMES      working
FORD       working
SCOTT      working
MILLER     working
BLAKE      working
MARTIN     working
CLARK      working
14 rows selected                  

add_months对日期增加或减少指定月份

语法：add_months(日期，月份)

--当前日期
SQL> SELECT ADD_MONTHS(SYSDATE)
  2  
SQL> SELECT SYSDATE FROM DUAL;
SYSDATE
-----------
2024/4/22 2
--当前日期+1个月
SQL> SELECT ADD_MONTHS(SYSDATE,1) FROM DUAL;
ADD_MONTHS(SYSDATE,1)
---------------------
2024/5/22 21:11:32

--当前日期-1个月
SQL> SELECT ADD_MONTHS(SYSDATE,-1) FROM DUAL;
ADD_MONTHS(SYSDATE,-1)
----------------------
2024/3/22 21:11:35

next_day 返回最近“星期几”是几号

注：最近的含义，如果今天周二，最近的周五就是本周五，最近的周一就是下周一

注：和to_char中的day是一样，代表星期几

语法：net_day（日期，星期几）

注：星期几，可以是数字；但如果写字符“星期几”需要注意字符集，中文字符集写中文，utf-8就必须写英文

--英文的星期四
SQL> SELECT NEXT_DAY(SYSDATE,'Thursday') FROM DUAL;
NEXT_DAY(SYSDATE,'THURSDAY')
----------------------------
2024/4/25 21:32:33

--数字星期四，(数字中一周是从周日开始的，所以周四是5)
SQL> SELECT NEXT_DAY(SYSDATE,5) FROM DUAL;
NEXT_DAY(SYSDATE,5)
-------------------
2024/4/25 21:37:11

round和trunc和字符函数一样的功能，跳过